package uk.ac.sanger.das.dasregistry.springrest.sources;

import java.math.BigInteger;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.biodas.jdas.dassources.Capabilities;
import org.biodas.jdas.dassources.utils.DasCoordinateSystemCollector;
import org.biodas.jdas.dassources.utils.DasSpec;
import org.biodas.jdas.dassources.utils.DasTimeFormat;
import org.biodas.jdas.dassources.utils.ProjectBean;
import org.biodas.jdas.dassources.utils.PropInterogator;
import org.biodas.jdas.dassources.utils.PropertyFactory;
import org.biodas.jdas.dassources.utils.RegistrySourceAdapter;
import org.biodas.jdas.exceptions.ValidationException;
import org.biodas.jdas.schema.servers.SERVER;
import org.biodas.jdas.schema.servers.SERVERS;
import org.biodas.jdas.schema.sources.CAPABILITY;
import org.biodas.jdas.schema.sources.COORDINATES;
import org.biodas.jdas.schema.sources.MAINTAINER;
import org.biodas.jdas.schema.sources.ObjectFactory;
import org.biodas.jdas.schema.sources.PROP;
import org.biodas.jdas.schema.sources.SOURCE;
import org.biodas.jdas.schema.sources.SOURCES;
import org.biodas.jdas.schema.sources.VERSION;
import org.biodas.jdas.util.CoordinateUtil;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.mysql.jdbc.Statement;

import uk.ac.sanger.das.dasregistry.springrest.coordinates.CoordinatesDao;
import uk.ac.sanger.das.dasregistry.springrest.sources.utils.RegistryTimer;

public class DasSourceDaoSangerRegistryJDAS extends SimpleJdbcDaoSupport
		implements DasSourceDao {

	static Logger logger = Logger
			.getLogger(DasSourceDaoSangerRegistryJDAS.class);
	private static final String regRootUrl = "http://www.dasregistry.org/";
	DasCoordinateSystemCollector coordmap;
	CoordinatesDao coordinatesDao;

	// public DasSourceDaoSangerRegistryJDAS(){
	// this.refreshSourcesFromDatabase();
	// }
	//
	//
	public CoordinatesDao getCoordinatesDao() {
		return coordinatesDao;
	}

	public void setCoordinatesDao(CoordinatesDao coordinatesDao) {
		this.coordinatesDao = coordinatesDao;
	}

//	private static String BASIC_SELECT = "SELECT "
//			+ " distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate,"
//			+ " r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin, r.specification, r.active "
//			+ " from registry r";
	private static String BASIC_SELECT ="SELECT distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate, r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin, r.specification, r.active, s.url from registry r left outer join server s on (r.server_id=s.server_id)";
	private DasSourceManagerSql sqlUtil = new DasSourceManagerSql();
	Long lastUpdate = new Long(0);// last time data was updated

	@Override
	public SOURCES getAllDasSources() {
		SOURCES sourcesCache;
		logger.info("updating sources in DasSourceDAO");
		ObjectFactory factory = new ObjectFactory();
		sourcesCache = factory.createSOURCES();
		coordmap = coordinatesDao.getAllCoordinateSystemsMap();
		String sqlServers = "";

		sqlServers = BASIC_SELECT;// + " where active=1";// limit 10";

		List<SOURCE> sources1 = getJdbcTemplate().query(sqlServers,
				new SourceMapper());

		sourcesCache.getSOURCE().addAll(sources1);
		return sourcesCache;
	}

	private class SourceMapper implements RowMapper<SOURCE> {

		private String regPrefix = "DS_";

		public SOURCE mapRow(ResultSet row, int rowNum) throws SQLException {

			SOURCE ds = new SOURCE();
			int auto_id = row.getInt("r.auto_id");
			String idString = regPrefix + auto_id;
			// logger.debug("id="+idString);
			String url = row.getString("r.url");
			String adminemail = row.getString("r.adminemail");
			String description = row.getString(4);
			Timestamp regTime = row.getTimestamp(5);
			Timestamp leaseTime = row.getTimestamp(6);
			String helperurl = row.getString(7);
			String nickname = row.getString(9);
			String alertMe = row.getString(11);
			String specification = row.getString("specification");
			int active = row.getInt("active");
			// System.out.println("DasSourceManager: converting the dates");
			java.util.Date registerDate = new Date(regTime.getTime());
			java.util.Date leaseDate = new Date(leaseTime.getTime());
			// ds.setId(idstring);
			ds.setUri(idString);
			// System.out.println(idString);
			ds.setTitle(nickname);
			ds.setDocHref(helperurl);

			MAINTAINER maintainer = new MAINTAINER();
			maintainer.setEmail(adminemail);
			ds.setMAINTAINER(maintainer);
			ds.setDescription(description);

			// spec and stated capabilities set up here
			List<String> capabilities = getCapabilities(auto_id,
					sqlUtil.getStatedCapabiltiesSql());
			VERSION version = new VERSION();
			version.setUri(idString);
			version.setCreated(DasTimeFormat.toDASString(registerDate));
			// set up coordinate system here
			List<COORDINATES> cs_names = getCoordinateSystemsForDasSource(
					auto_id, coordmap);
			// System.out.println("adding coord="+cs_names.toString());
			for (COORDINATES coordinate : cs_names) {
				// System.out.println(coordinate.getAuthority());
				// System.out.println(coordinate.getContent());
				// System.out.println(coordinate.getSource());
				// System.out.println(coordinate.getTaxid());
				// System.out.println(coordinate.getTestRange());
				// System.out.println(coordinate.getUri());
				// System.out.println(coordinate.getVersion());
				version.getCOORDINATES().add(coordinate);
			}

			for (String cap : capabilities) {
				CAPABILITY capab = new CAPABILITY();
				capab.setQueryUri(url + cap);
				capab.setType("das1:" + cap);
				version.getCAPABILITY().add(capab);
			}

			List<String> validCapabilities = getCapabilities(auto_id,
					sqlUtil.getValidCapabilitiesSql());

			for (String cap : validCapabilities) {
				PROP property = new PROP();
				property.setName(cap);
				property.setValue("valid");
				version.getPROP().add(property);
			}

			PROP specProperty = new PROP();
			specProperty.setName("spec");
			if (specification != null) {

				specProperty.setValue(DasSpec.convertToRegistrySpecification(
						specification).toString());
			} else {
				specProperty.setValue(DasSpec.SPEC1_53E.toString());
			}
			version.getPROP().add(specProperty);
			PROP leaseDateProp = new PROP();
			leaseDateProp.setName("leaseTime");
			leaseDateProp.setValue(DasTimeFormat.toDASString(leaseDate));
			version.getPROP().add(leaseDateProp);

			if (alertMe.equals("1")) {
				PROP alertMeProp = new PROP();
				alertMeProp.setName("alertMe");
				alertMeProp.setValue(alertMe);
				version.getPROP().add(alertMeProp);
			}
			List<String> labels = getLabels(auto_id);
			String labelName = "label";
			for (String label : labels) {
				PROP labelProp = new PROP();
				labelProp.setName(labelName);
				labelProp.setValue(label);
				version.getPROP().add(labelProp);
			}
			if (active == 0) {
				RegistryTimer timer = new RegistryTimer();
				int daysInactive = timer.daysInactive(DasTimeFormat
						.toDASString(leaseDate));
				PROP inactive = PropertyFactory.createProperty("inactive",
						Integer.toString(daysInactive));
				version.getPROP().add(inactive);
			}
			String sourcesDoc=row.getString("s.url");
			if(sourcesDoc!=null && sourcesDoc!=""){
				PROP sourcesDocProp = new PROP();
				sourcesDocProp.setName("sourcesDoc");
				sourcesDocProp.setValue(sourcesDoc);
				version.getPROP().add(sourcesDocProp);
			}
			version.getPROP().addAll(getProjectProperitesForURL(url));

			// Map<Capabilities, Long> times = getCapabilityTimes(auto_id);
			// Set <Capabilities>keys=times.keySet();
			// for(Capabilities cap:keys){
			// Long time=times.get(cap);
			// if(time!=0){
			// version.getPROP().add(PropertyFactory.createProperty(cap.getName()+"_time",
			// Long.toString(time)));
			// }
			// }
			ds.getVERSION().add(version);

			return ds;
		}
	}

	public class CapabilitiesMapper implements
			RowMapper<Map<Capabilities, Integer>> {

		Map<Capabilities, Integer> times = new HashMap<Capabilities, Integer>();

		public Map<Capabilities, Integer> mapRow(ResultSet row, int rowNum)
				throws SQLException {

			for (Capabilities value : EnumSet.allOf(Capabilities.class)) {
				times.put(value, row.getInt(value.getCommand()));

				// params.put(value.getCommand(),
				// times.get(value));
			}

			return times;
		}
	}

	public Map<Capabilities, Long> getCapabilityTimes(int auto_id) {

		String startsql = "select ";
		String select = " auto_id";
		for (Capabilities value : EnumSet.allOf(Capabilities.class)) {
			select += "," + value.getCommand() + " ";

			// params.put(value.getCommand(), times.get(value));
		}
		String end = " from capability_times  where auto_id=" + auto_id;
		String sql = startsql + select + end;
		// System.out.println(sql);
		Map<Capabilities, Long> times = new HashMap<Capabilities, Long>();
		try {
			times = (Map<Capabilities, Long>) this.getJdbcTemplate()
					.queryForObject(sql, new RowMapper() {

						Map<Capabilities, Long> times = new HashMap<Capabilities, Long>();

						public Object mapRow(ResultSet rs, int rowNum)
								throws SQLException {
							for (Capabilities value : EnumSet
									.allOf(Capabilities.class)) {
								times.put(value, rs.getLong(value.getCommand()));

								// params.put(value.getCommand(),
								// times.get(value));
							}

							return times;
						}
					});
		} catch (DataAccessException e) {
			// e.printStackTrace();
			// if no result returned spring throws exception which is a bit
			// rubbish and so we just return a new made up object
			return new HashMap<Capabilities, Long>();

		}
		return times;

	}

	private List<String> getCapabilities(int auto_id, String sql) {
		// System.out.println("get_capabilities");
		try {
			List<String> capabilities = new ArrayList<String>();
			// System.out.println(sql);

			Map<Capabilities, Integer> result = getCapabilitiesMap(sql + " "
					+ auto_id);
			Set<Capabilities> keys = result.keySet();
			for (Capabilities cap : keys) {
				if (result.get(cap) == 1) {
					capabilities.add(cap.getName());
				}

			}
			return capabilities;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	private Map<Capabilities, Integer> getCapabilitiesMap(String sql) {
		Map<Capabilities, Integer> times = new HashMap<Capabilities, Integer>();
		// System.out.println(sql);
		try {
			times = this.getJdbcTemplate().queryForObject(sql,
					new CapabilitiesMapper());
		} catch (DataAccessException e) {
			// e.printStackTrace();
			// if no result returned spring throws exception which is a bit
			// rubbish and so we just return a new made up object
			return new HashMap<Capabilities, Integer>();

		}
		return times;
	}

	/*
	 * helper function for get_capabilities
	 */
	private boolean hasCapability(ResultSet row, String capability) {
		try {
			int cap = row.getInt(capability);

			if (cap > 0) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;

	}

	/*
	 * retrieve all coordinate systems that are provided by service auto_id
	 */
	private List<COORDINATES> getCoordinateSystemsForDasSource(int auto_id,
			DasCoordinateSystemCollector coordmap) throws SQLException {
		List<COORDINATES> lst = new ArrayList<COORDINATES>();

		// todo change to new style ..
		String sql = "select  coordNS_auto, testregion "
				+ " from registry2coordNS where registry2coordNS.auto_id = "
				+ auto_id;

		List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);

		Iterator row = rows.iterator();
		while (row.hasNext()) {
			Map<String, Object> result = (Map<String, Object>) row.next();
			int coordNS_auto = (Integer) result.get("coordNS_auto");

			String testregion = (String) result.get("testregion");

			COORDINATES csname = coordmap.get(new Integer(coordNS_auto));
			if (testregion == null) {
				testregion = "";
			}
			csname.setTestRange(testregion);
			lst.add(csname);

		}
		return lst;

	}

	public java.util.Date getLastModified() {
		String sql = "select lastModified from admin where adid = 1";
		java.util.Date date = new Date(0);
		SqlRowSet rows = getJdbcTemplate().queryForRowSet(sql);

		while (rows.next()) {
			// System.out.println("adding label" + (String) row.get("label"));
			Timestamp t = rows.getTimestamp("lastModified");
			// logger.debug("t=" + t.toString());

			if (t != null) {

				date = new java.util.Date(t.getTime());
			}

			return date;
		}
		return date;
	}

	public class CoordinatesMapper implements
			RowMapper<Map<Capabilities, Integer>> {

		Map<Capabilities, Integer> times = new HashMap<Capabilities, Integer>();

		public Map<Capabilities, Integer> mapRow(ResultSet row, int rowNum)
				throws SQLException {

			for (Capabilities value : EnumSet.allOf(Capabilities.class)) {
				times.put(value, row.getInt(value.getCommand()));

				// params.put(value.getCommand(),
				// times.get(value));
			}

			return times;
		}
	}

	private List<String> getLabels(int auto_id) throws SQLException {
		String sql = "select label.label from registry2label,label where registry2label.auto_id =? "
				+ " and registry2label.label_auto = label.label_auto";

		List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql,
				auto_id);
		List<String> lst = new ArrayList<String>();
		for (Map row : rows) {
			// System.out.println("adding label" + (String) row.get("label"));
			lst.add((String) row.get("label"));
		}

		return lst;

	}

	public List<PROP> getProjectProperitesForURL(String das_source_url)
			throws SQLException {
		List<PROP> list = new ArrayList();

		String sql1 = "select project_id from registry where url = ?";
		int projectId = getJdbcTemplate().queryForInt(sql1, das_source_url);
		if (projectId != 0) {
			String sql2 = "select name,iconurl,homepage,description,user_id,project_id from project where project_id = ?";
			ProjectBean project = getJdbcTemplate().queryForObject(sql2,
					new ProjectMapper(), projectId);

			PropertyFactory factory = new PropertyFactory();
			list.add(factory.createProperty("projectName", project.getName()));
			list.add(factory.createProperty("projectDesc",
					project.getDescription()));
			list.add(factory.createProperty("projectIcon", project.getIconURL()
					.toString()));
			list.add(factory.createProperty("projectHome", project
					.getHomepage().toString()));
		}
		return list;

	}

	private class ProjectMapper implements RowMapper<ProjectBean> {

		@Override
		public ProjectBean mapRow(ResultSet row, int rownumber)
				throws SQLException {
			ProjectBean projectBean = new ProjectBean();

			projectBean.setName(row.getString("name"));
			projectBean.setDescription(row.getString("description"));
			projectBean.setProjectID(row.getInt("project_id"));
			try {

				projectBean.setHomepage(new URL(row.getString("homepage")));

			} catch (MalformedURLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				projectBean.setIconURL(new URL(regRootUrl
						+ row.getString("iconurl")));
			} catch (MalformedURLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			// projectBean.setOwner(owner)

			return projectBean;
		}
	}

	@Override
	public SOURCES add(SOURCES sources) {
		// should not already exist and should be valid as already checked by
		// the sourcesManager
		logger.info("creating source in DasSourceDao");
		SOURCE source = sources.getSOURCE().get(0);// currently only adding one
		// source per request
		RegistrySourceAdapter ra = new RegistrySourceAdapter(source);
		String serverUri = getServerOrSourcesUri(ra);
		
		int server_id = this.getServerId(serverUri);

		logger.warn("server_id=" + server_id);

		String originalUri = null;
		try {
			originalUri = ra.getOriginalDataSourceUri();
			if(!originalUri.endsWith("/"))originalUri=originalUri+"/";
		} catch (ValidationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		logger.debug("uri=" + originalUri);
		logger.debug("title=" + source.getTitle());

		logger.debug("maintainer=" + source.getMAINTAINER().getEmail());

		logger.debug("description=" + source.getDescription());

		String doch = source.getDocHref();

		logger.debug("docHref=" + doch);

		// prefix is depending if das source is local or not;

		// do the same for all sources now when first adding to database as
		// we don't want to treat mirrored sources any different
		// ps.setString(7, null);

		// ps.setInt(6, 1);
		// ps.setInt(7, server_id);
		// logger.debug("serverId="+server_id);
		// ps.setInt(8, 1);// set active to 1
		// logger.warn(sql);

		String sql = "INSERT INTO registry " + "(url," + "nickname,"
				+ "adminemail," + "description," + "registerDate,"
				+ "leaseDate," + "helperurl," + "alert_admin,"
				+ "server_id, active";

		String endsql = ") values ('" + originalUri + "','" + source.getTitle()
				+ "','" + source.getMAINTAINER().getEmail() + "','"
				+ source.getDescription() + "',now(),now(),'"
				+ source.getDocHref() + "','" + 1 + "','" + server_id + "','"
				+ 1 + "'";

		List<CAPABILITY> caps = source.getVERSION().get(0).getCAPABILITY();
		for (CAPABILITY cap : caps) {
			// potential security problem ?
			// no, because all capabilities are checked before
			// and unknown are not allowed
			// if dsn ignore
			if (!cap.getType().equals("das1:dsn")) {
				logger.debug(cap.getType());
				sql += ","
						+ cap.getType().substring(
								cap.getType().indexOf(":") + 1,
								cap.getType().length());
				endsql += ",1";
			}
		}
		int auto_id = -9999;
		// if ( !ds.getId().equals(DasSource.EMPTY_ID)){
		// auto_id = getDbAutoId(ds.getId());
		// sql += ",auto_id";
		// endsql += ",?";
		// }
		sql += endsql + ");";
		String url = source.getUri();
		char lastChar = source.getUri().charAt(source.getUri().length() - 1);
		if (lastChar != '/') {
			url += "/";
		}
		logger.debug(sql);

		// System.out.println(sql);
		Connection conn = this.getConnection();

		ResultSet rs = null;
		java.sql.Statement statement = null;
		try {

			statement = conn.createStatement();
			statement.executeUpdate(sql,
					java.sql.Statement.RETURN_GENERATED_KEYS);
			rs = statement.getGeneratedKeys();

			if (rs.next()) {
				auto_id = rs.getInt(1);
			}

			logger.warn("register new source with auto_id " + auto_id);

			List<COORDINATES> coordsForSource = source.getVERSION().get(0)
					.getCOORDINATES();
			//
			for (COORDINATES coord : coordsForSource) {

				String prefix = "DS";

				selectInsertCoordinateSystem(conn, coord, auto_id, prefix);

			}
			rs.close();
			statement.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {

				try {

					rs.close();

				} catch (SQLException e) { /* ignored */
				}

			}

			if (statement != null) {

				try {

					statement.close();

				} catch (SQLException e) { /* ignored */
				}

			}

			if (conn != null) {

				try {

					conn.close();

				} catch (SQLException e) { /* ignored */
				}

			}

		}

		//
		// insertLabels(conn, auto_id, labels);
		// logger
		// .info("adding validated capabilities to db during registering of source");
		// updateValidationStatusInDb(conn, auto_id, ds, prefix);
		// updateSpecificationStatusInDb(conn, auto_id, ds.getSpecification());

		// System.out.println("cs inserted, Labels added");

		source.setUri("DS_" + auto_id);
		// if we have updated it can i access the current sources cache and
		// update the single source?

		// sourcesCache.getSOURCE().add(source);//add the succesfully created
		// source to the cache so we don't have to wait for the cache refresh

		// sources.getSOURCE().get(0);
		modified();
		return sources;

	}

	private String getServerOrSourcesUri(RegistrySourceAdapter ra) {
		String serverUri = "";

		String sourcesDocUri = ra.getSourcesDoc();
		if (!sourcesDocUri.equals("")) {
			serverUri = sourcesDocUri;
		}else{
			//if no sources doc specified by mirroring then get the server uri from the root of the das url
			try {
	            serverUri = ra.getServerUri();
	        } catch (ValidationException e1) {
	            // TODO Auto-generated catch block
	            e1.printStackTrace();
	        }
		}
		return serverUri;
	}

	/**
	 * delete a DS_ id
	 */
	@Override
	public int delete(String sourceUri) {
		String sql = "update registry set active=0 where auto_id=?";
		sourceUri = sourceUri.replace("DS_", "");// remove DS so we just have
													// the auto_id
		modified();
		return getJdbcTemplate().update(sql, sourceUri);
	}

	@Override
	public SOURCES update(SOURCES sourcesForUpdate) {
		SOURCE source = sourcesForUpdate.getSOURCE().get(0);
		String uri = source.getUri();
		logger.debug("source getUri in sourceDao=" + uri);// uri should be a DS_
		// id
		String number = uri.substring(3, uri.length());
		logger.debug("number for DS_ to auto_id = " + number);
		int auto_id = Integer.valueOf(number);
		String sql = "UPDATE registry set " + "url=?, " + "nickname=?, "
				+ "adminemail=?, " + "description=?, " + "leaseDate=now(), "
				+ "helperurl=?, " + "alert_admin=?,"
				+ "specification=?, server_id=?, active=1";
		int rowcount = 0;
		List<CAPABILITY> caps = source.getVERSION().get(0).getCAPABILITY();
		String endsql = "";
		for (Capabilities cap : Capabilities.values()) {
			boolean found = false;
			String curr_cap = cap.toString();
			for (int i = 0; i < caps.size(); i++) {
				CAPABILITY mycap = caps.get(i);
				if (mycap
						.getType()
						.substring(mycap.getType().indexOf(":") + 1,
								mycap.getType().length()).equals(curr_cap)) {
					found = true;
				}
			}
			sql += "," + curr_cap;
			if (found) {

				sql += "=1";
			} else {
				sql += "=NULL";
			}

		}

		sql += endsql + " where auto_id = ?";

		Connection conn = this.getConnection();
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(sql);

			RegistrySourceAdapter sAd = new RegistrySourceAdapter(source);
			String url = null;
			try {
				url = sAd.getOriginalDataSourceUri();
			} catch (ValidationException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}

			char lastChar = source.getUri()
					.charAt(source.getUri().length() - 1);
			if (lastChar != '/') {
				url += "/";
			}
			logger.warn("updating source in Dao with url or DS_ id =" + url);
			logger.debug(sql);

			ps.setString(1, url);
			ps.setString(2, source.getTitle());
			ps.setString(3, source.getMAINTAINER().getEmail());
			ps.setString(4, source.getDescription());
			ps.setString(5, source.getDocHref());
			// ps.setString(6,testCode);
			ps.setInt(6, 1);
			RegistrySourceAdapter ra=new RegistrySourceAdapter(source);
			List<PROP> props = source.getVERSION().get(0).getPROP();
			String spec = PropInterogator.getPropByName("spec", props);
			logger.debug("spec=" + spec);
			DasSpec dasSpec = DasSpec.convertToRegistrySpecification(spec);
			ps.setString(7, dasSpec.toString());
			String serverUri=this.getServerOrSourcesUri(ra);
			
			ps.setInt(8, this.getServerId(serverUri));
			ps.setInt(9, auto_id);
			
			// execute statement ...

			rowcount = ps.executeUpdate();

			List<COORDINATES> coordsForSource = source.getVERSION().get(0)
					.getCOORDINATES();
			//
			for (COORDINATES coord : coordsForSource) {

				String prefix = "DS";

				removedCachedSecondaryData(conn, auto_id);
				selectInsertCoordinateSystem(conn, coord, auto_id, prefix);
				// updateValidationStatusInDb(conn, auto_id, source, prefix);

			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		logger.warn("updated rows " + rowcount);
		// updateCache(source, url);
		logger.warn("update source with auto_id " + auto_id);

		modified();
		return sourcesForUpdate;
	}

	@Override
	public int activate(String sourceUri) {
		String sql = "update registry set active=1 where auto_id=?";
		sourceUri = sourceUri.replace("DS_", "");// remove DS so we just have
													// the auto_id
		modified();
		return getJdbcTemplate().update(sql, sourceUri);
	}

	/**
	 * tell the database the sources or coodinate system has changed
	 */
	private void modified() {
		logger.debug("modified now");
		String sql = "update admin set lastModified=now()";
		getJdbcTemplate().update(sql);

	}

	@Override
	public SERVERS getServers() {
		String sqlServers = "select server_id, url, user_id, description, number_of_sources from server order by number_of_sources desc";
		List<SERVER> serversList = null;
		serversList = getJdbcTemplate().query(sqlServers,
				new RowMapper<SERVER>() {

					public SERVER mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						SERVER server = new SERVER();
						server.setUri(rs.getString("url"));
						server.setDescription(rs.getString("description"));
						if (rs.getInt("user_id") != 0) {
							server.setServerControlled("yes");
						}
						server.setNumberOfSources(new BigInteger(Integer
								.toString(rs.getInt("number_of_sources"))));
						return server;
					}
				});

		SERVERS servers = new org.biodas.jdas.schema.servers.ObjectFactory()
				.createSERVERS();
		servers.getSERVER().addAll(serversList);
		return servers;
	}

	private int getServerId(String serverUri) {
		if(!serverUri.endsWith("/")){
			serverUri =serverUri+ "/";
		}
		String sql = "select server_id from server where url=?";

		// if (serverUri.contains("ensembl.org/das/")) {
		// if (!serverUri.equals("http://www.ensembl.org/das/")) {
		// serverUri = "http://localhost:8080/EnsemblDasProxy/das/";
		// }
		// }
		System.out.println("serverUriblah ="+serverUri);
		int result = getJdbcTemplate().queryForInt(sql, serverUri);
		logger.debug("server_id=" + result);
		return result;

	}

	/**
	 * 
	 * @param conn
	 * @param coordsys
	 * @param auto_id
	 * @param prefix
	 * @throws SQLException
	 */
	public void selectInsertCoordinateSystem(Connection conn,
			COORDINATES coordsys, int auto_id, String prefix)
			throws SQLException {

		logger.debug("checking if coordinate system is in the registry for coordsys="
				+ coordsys
				+ " for auto_id= "
				+ auto_id
				+ " with prefix="
				+ prefix);
		// now we have modified coordinate systems
		// map the strings to the ids...
		// DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
		// conn, prefix);

		// now map the coordsys string to the proper coordNS_auto value
		Set s = coordmap.keySet();
		Iterator iter = s.iterator();
		int coordNS_auto = -9999;
		// logger.debug("checking " + coordsys);
		while (iter.hasNext()) {
			Integer key = (Integer) iter.next();
			COORDINATES csname = coordmap.get(key);
			// System.out.println(" comparing with " + csname);
			// System.out.println(csname.getUniqueId() + " " +
			// coordsys.getUniqueId());

			if (CoordinateUtil.getOrganismName(csname).equalsIgnoreCase(
					CoordinateUtil.getOrganismName(coordsys))) {
				// logger.debug("organisms the same:"
				// + CoordinateUtil.getOrganismName(coordsys));

				if (CoordinateUtil.equals(csname, coordsys)) {
					// that's the one we are talking about
					coordNS_auto = key.intValue();
					logger.debug("intvalue found " + coordNS_auto);
					break;
				}
			}
		}

		if (coordNS_auto == -9999) {
			throw new SQLException(
					"could not find coordinate system in database >" + coordsys
							+ "<");
		}

		// create new entry in registry2coordNS table;
		logger.info("creating a new entry in the registry2coordNS table");
		logger.debug("coords update= auto_id=" + auto_id + " coordNS_auto="
				+ coordNS_auto + " testcode=" + coordsys.getTestRange());
		String sql = "INSERT INTO registry2coordNS ( auto_id, coordNS_auto,testregion ) values (?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, auto_id);
		ps.setInt(2, coordNS_auto);
		ps.setString(3, coordsys.getTestRange());
		ps.executeUpdate();
		ps.close();

	}

	/**
	 * remove data related to the registry2xyz tables ...
	 * 
	 * @param conn
	 *            the database connection
	 * @param auto_id
	 *            the auto_id of the DasSource in the db- registry table
	 * @throws SQLException
	 */
	public void removedCachedSecondaryData(Connection conn, int auto_id)
			throws SQLException {

		String cmd2 = "delete from registry2coordNS where auto_id=?";
		doAutoIdSql(conn, cmd2, auto_id);

		String cmd3 = "delete from registry2label where auto_id=?";
		doAutoIdSql(conn, cmd3, auto_id);

		// also we now need to delete from the validated_capabilities table
		String cmd4 = "delete from validated_capabilities where auto_id=?";
		doAutoIdSql(conn, cmd4, auto_id);

		String cmd5 = "delete from types where auto_id=?";
		doAutoIdSql(conn, cmd5, auto_id);

		// TODO: registry2organism is obsolete
		// String cmd4 = "delete from registry2organism where auto_id=?";
		// doAutoIdSql(conn,cmd4,auto_id);

	}

	private void doAutoIdSql(Connection conn, String sqlcmd, int auto_id)
			throws java.sql.SQLException {
		PreparedStatement ps = conn.prepareStatement(sqlcmd);
		ps.setInt(1, auto_id);
		ps.executeUpdate();
		ps.close();
	}

	// public boolean updateValidationStatusInDb(Connection conn, int auto_id,
	// SOURCE ds, String prefix) throws SQLException {
	//
	// logger.logger("updating validation status of das source " + auto_id
	// + " " + ds.getTitle());
	//
	// String url = ds.getUri();
	// String nickname = ds.getTitle();
	//
	// // need to check if the auto_id is already in the table of stored valid
	// // sources
	// // build up UPDATE statement ...
	// String updateOrInsert = "UPDATE";
	// if (!this.doesAutoIdExist(conn, auto_id)) {
	// updateOrInsert = "INSERT";
	// }
	// String sql = updateOrInsert + " validated_capabilities set auto_id=? ";
	// // add the capabilities ..
	//
	// List<String> validCapabilitiesStrings = ds.getValidCapabilities();
	// List<Capabilities>
	// validCapabilities=Capabilities.capabilitiesListFromStrings(validCapabilitiesStrings);
	// boolean noValidCaps = false;
	// if (validCapabilities == null) {
	// // there are no valid capabilities for this server
	// noValidCaps = true;
	// }
	// for (Capabilities cap : Capabilities.values()) {
	// boolean found = false;
	// String curr_cap = cap.toString();
	// //System.out.println("curr_cap="+curr_cap);
	// if (curr_cap.equalsIgnoreCase("dna"))
	// continue;// ignore dna as not been valid for years!
	// if (noValidCaps) {
	// found = false;
	// } else {
	// for (int i = 0; i < validCapabilities.size(); i++) {
	//
	// Capabilities mycap = validCapabilities.get(i);
	// //System.out.println(mycap.getCommand());
	// if (mycap.getCommand().equals(curr_cap)) {
	// found = true;
	// }
	//
	// }
	// }
	// sql += "," + curr_cap;
	// if (found) {
	//
	// sql += "=1";
	// } else {
	// sql += "=NULL";// TODO set this to 0 once testing of unstated
	// // capabilities is implemented
	// }
	//
	// }
	//
	// if (updateOrInsert.equalsIgnoreCase("update")) {
	// sql += " where auto_id = ?";
	// }
	//
	// logger.debug(sql);
	// //System.out.println("validated capabilities sql="+sql);
	//
	// PreparedStatement ps = conn.prepareStatement(sql);
	//
	// ps.setInt(1, auto_id);
	//
	// if (updateOrInsert.equalsIgnoreCase("update")) {
	// ps.setInt(2, auto_id);
	// }
	// // execute statement ...
	// ps.executeUpdate();
	//
	// ps.close();
	//
	// return true;
	//
	// }
	public boolean doesAutoIdExist(Connection conn, int auto_id) {
		try {
			PreparedStatement ps = conn
					.prepareStatement("SELECT COUNT(*) FROM validated_capabilities where auto_id = "
							+ auto_id);
			ResultSet row = ps.executeQuery();
			row.next();
			int size = row.getInt(1);
			ps.close();
			row.close();
			if (size > 0) {
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
}